using System.Diagnostics;

namespace SharpTalk.Core.Data.SqLiteDataProvider
{
    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Reflection;
    using Diagnostic;
    using System.Data.SQLite;

    /// <summary>
    /// 
    /// </summary>
    public abstract class SqLiteDataProviderBase<T> 
        : Traceable,  IDataProvider<T>
    {
        
        #region Command strings

        internal const string CMD_USERS_CREATE = "Insert into Users (Guid,FirstName,LastName,NickName,Image,Status,Uri,LastUpdate) values (?,?,?,?,?,?,?,?)";
        internal const string CMD_USERS_READ_ALL = "Select Guid,FirstName,LastName,NickName,Image,Status,Uri From Users";
        internal const string CMD_USERS_READ_GUID = "Select Guid,FirstName,LastName,NickName,Image,Status,Uri From Users Where Guid=?";
        internal const string CMD_USERS_UPDATE = "Update Users Set FirstName=?, LastName=?, NickName=?, Image=?, Status=?, Uri=?, LastUpdate=? where Guid=?";
        internal const string CMD_USERS_DELETE = "Delete from Users where Guid=?";
        internal const string CMD_USERS_DELETE_ALL = "Delete from Users";
        internal const string CMD_USERS_READ_CHATROOMUSER = "Select Guid,FirstName,LastName,NickName,Image,Status,Uri From Users Inner join ChatRoomUsers on ChatRoomUsers.User=Guid Where ChatRoomUsers.ChatRoom=?";


        internal const string CMD_TRACKINGS_CREATE = "Insert into Trackings (Tracked,Tracker) values (?,?)";
        internal const string CMD_TRACKINGS_READ_ALL = "Select Tracked,Tracker from Trackings";
        internal const string CMD_TRACKINGS_READ_TRACKED = "Select Guid,FirstName,LastName,NickName,Image,Status,Uri from Trackings Inner join Users on Users.Guid=Trackings.Tracker Where Tracked=?";
        internal const string CMD_TRACKINGS_DELETE_TRACKED = "Delete from Trackings where Tracked=?";
        internal const string CMD_TRACKINGS_DELETE_TRACKER = "Delete from Trackings where Tracker=?";
        internal const string CMD_TRACKINGS_DELETE_ALL = "Delete from Trackings";

        internal const string CMD_CHATROOMS_CREATE = "Insert into ChatRooms (Guid,Type,Owner,Name,Image,PasswordHash,IsDefault,LastUpdate,Motd) values (?,?,?,?,?,?,?,?,?)";
        internal const string CMD_CHATROOMS_READ_ALL = "Select Guid,Type,Owner,Name,Image,PasswordHash,IsDefault,Motd from ChatRooms";
        internal const string CMD_CHATROOMS_READ_GUID = "Select Guid,Type,Owner,Name,Image,PasswordHash,IsDefault,Motd from ChatRooms Where Guid=?";
        internal const string CMD_CHATROOMS_READ_TYPE = "Select Guid,Type,Owner,Name,Image,PasswordHash,IsDefault,Motd from ChatRooms Where Type=?";
        internal const string CMD_CHATROOMS_READ_NAME = "Select Guid,Type,Owner,Name,Image,PasswordHash,IsDefault,Motd from ChatRooms Where Name=?";
        internal const string CMD_CHATROOMS_READ_TYPE_NAME = "Select Guid,Type,Owner,Name,Image,PasswordHash,IsDefault,Motd from ChatRooms Where Type=? and Name=?";
        internal const string CMD_CHATROOMS_READ_OWNER = "Select Guid,Type,Owner,Name,Image,PasswordHash,IsDefault,Motd from ChatRooms Where Owner=?";
        internal const string CMD_CHATROOMS_UPDATE = "Update ChatRooms Set PasswordHash=?, Image=?,LastUpdate=?,Motd=?,Type=? where Guid=?";
        internal const string CMD_CHATROOMS_UPDATE_NO_PASSWORD = "Update ChatRooms Set Image=?,LastUpdate=?,Motd=?,Type=? where Guid=?";
        internal const string CMD_CHATROOMS_DELETE_ALL = "Delete from ChatRooms";
        internal const string CMD_CHATROOMS_DELETE_GUID = "Delete from ChatRooms where Guid=?";
        internal const string CMD_CHATROOMS_DELETE_OWNER = "Delete from ChatRooms where Owner=?";

        internal const string CMD_CHATROOMUSERS_CREATE = "Insert into ChatRoomUsers (ChatRoom,User) values (?,?)";
        internal const string CMD_CHATROOMUSERS_READ_ALL = "Select ChatRoom,User from ChatRoomUsers";
        internal const string CMD_CHATROOMUSERS_READ_CHATROOM = "Select ChatRoom,User from ChatRoomUsers Where ChatRoom=?";
        internal const string CMD_CHATROOMUSERS_READ_USER = "Select ChatRoom,User from ChatRoomUsers Where User=?";
        internal const string CMD_CHATROOMUSERS_DELETE = "Delete from ChatRoomUsers Where ChatRoom=? And User=?";
        internal const string CMD_CHATROOMUSERS_DELETE_ALL = "Delete from ChatRoomUsers";
        internal const string CMD_CHATROOMUSERS_DELETE_CHATROOM = "Delete from ChatRoomUsers Where ChatRoom=?";
        internal const string CMD_CHATROOMUSERS_DELETE_USER = "Delete from ChatRoomUsers Where User=?";
        internal const string CMD_CHATROOMUSERS_DELETE_OWNER = "Delete from ChatRoomUsers Where ChatRoom in (Select Guid From ChatRooms Where Owner=?)";

        #endregion

        private bool _disposed;
        private SQLiteConnection _connection;

        /// <summary>
        /// Creates a new instance
        /// </summary>
        /// <param name="filepath">The full path to the db file</param>
        /// <exception cref="ArgumentNullException">The filepath is null or empty or the database file doesn't exist.</exception>
        protected SqLiteDataProviderBase(string filepath)
        {
            if (string.IsNullOrEmpty(filepath) ||
                !File.Exists(filepath)) throw new ArgumentNullException("filepath");

            _connection = new SQLiteConnection(String.Format("Data Source={0}",Path.GetFullPath(filepath)));
        }
       
        /// <summary>
        /// Creates a new instance
        /// </summary>
        /// <param name="filepath">The full path to the db file</param>
        /// <param name="traceProvider">A TraceProvider instance or null</param>
        /// <exception cref="ArgumentNullException">The filepath is null or empty or the database file doesn't exist.</exception>
        protected SqLiteDataProviderBase(string filepath, ITraceProvider traceProvider)
            : this(filepath)
        {
            TraceProvider = traceProvider;
        }
       
        /// <summary>
        /// Determines if the instance has been disposed
        /// </summary>
        internal bool Disposed
        {
            get { return _disposed; }
        }
        /// <summary>
        /// Obtains the internal SQLiteConnection instance
        /// </summary>
        internal SQLiteConnection Connection
        {
            get { return _connection; }
        }

        /// <summary>
        /// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
        /// </summary>
        /// <filterpriority>2</filterpriority>
        public virtual void Dispose()
        {
            if (ShouldTrace(TraceEventType.Verbose))TraceMethodCall(MethodBase.GetCurrentMethod());

            if(_connection == null || _disposed ) return;

            _connection.Dispose();
            _connection = null;
            TraceProvider = null;
            _disposed = true;
            GC.SuppressFinalize(this);
        }
        /// <summary>
        /// Initializes the instance
        /// </summary>
        public virtual void Initialize()
        {
            if (ShouldTrace(TraceEventType.Verbose))TraceMethodCall(MethodBase.GetCurrentMethod());
            _connection.Open();
        }

        public abstract void ClearAll();
        public abstract bool Save(T obj);
        public abstract List<T> All();
        public abstract T Update(T obj);
        public abstract bool Delete(T obj);
        public abstract bool Delete(Guid guid);
        public abstract T Find(Guid guid);
    }
}